晚上試作了一下,
我的解法也跟你一樣...

哈哈哈，我也一樣!!

DECLARE @Employee TABLE 
(
	Id INT,
	Name NVARCHAR(50),
	Salary INT,	
	DepartmentId INT
);

DECLARE @Department TABLE 
(
	Id INT,
	Name NVARCHAR(50)
);

INSERT INTO @Employee
    (Id, Name, Salary, DepartmentId) 
VALUES
    (1, N'Joe', 70000, 1),
    (2, N'Henry', 80000, 2),
    (3, N'Sam', 60000, 2),
    (4, N'Max', 90000, 1)

INSERT INTO @Department
    (Id, Name) 
VALUES
    (1, N'IT'),
    (2, N'Sales')

SELECT C.Name,
       A.Name,
	   A.Salary
FROM @Employee AS A
LEFT JOIN (
	SELECT A.DepartmentId,
	       MAX(A.Salary) AS Salary
	FROM @Employee AS A
	GROUP BY A.DepartmentId
) AS B ON B.DepartmentId=A.DepartmentId AND B.Salary=A.Salary
LEFT JOIN @Department AS C ON C.Id=A.DepartmentId
WHERE B.Salary IS NOT NULL
ORDER BY A.DepartmentId

恩~~我也玩玩了@@~

declare @Employee table(
	Id int
	,Name nvarchar(50)
	,Salary int
	,DepartmentId int
)
insert into @Employee
values(1, N'Joe', 70000, 1),(2, N'Henry', 80000, 2),(3, N'Sam', 60000, 2),(4, N'Max', 90000, 1)

declare @Department table(
	Id int
	,Name nvarchar(50)
)
insert into @Department
values(1, N'IT'),(2, N'Sales')

select Name,Salary
from (
	select Row_Number() Over(Partition by a.Id Order by b.Salary desc) as Sort
	,a.Name
	,b.Salary
	from @Department as a
	left join @Employee as b on a.Id = b.DepartmentId
) as k
where Sort = 1

第二種...像這樣吧

select a.Name
,isNull((
	select top 1 Salary
	from @Employee as b 
	where a.Id = b.DepartmentId
	order by Salary desc
),0) as Salary
from @Department as a

這樣也行..

select a.Name
,isNull((
	select max(Salary)
	from @Employee as b 
	where a.Id = b.DepartmentId
),0) as Salary
from @Department as a

如果是T-SQL寫法的話~應該是這樣吧XD..

declare @Detail table(
	Department_Name nvarchar(50)
	,Employee_Name nvarchar(50)
	,Salary int
)

declare @i int,@Max int
declare @Index int,@Department_Name nvarchar(50)
declare @Employee_Name nvarchar(50),@Salary int
set @i = 1

select @Max = isNull(count(*),0)
from @Department

while(@i<=@Max)
begin
	select @Index=Id
	,@Department_Name=Name
	from (
		select Row_Number() Over(order by Id) as Sort
		,Id
		,Name
		from @Department
	) as k
	where Sort = @i

	select top 1
	@Employee_Name=Name
	,@Salary=Salary
	from @Employee
	where DepartmentId = @Index
	order by Salary desc

	insert into @Detail
	values(@Department_Name,@Employee_Name,@Salary)

	set @Employee_Name = ''
	set @Salary = 0
	set @i = @i +1
end

select * from @Detail

這個我參考darwin0616分享的方式改寫的~也是可以用這方式@@...

SELECT
    b.Name AS 'Department',
    a.Name AS 'Employee',
    Salary
FROM
    @Employee as a
        JOIN
    @Department as b ON a.DepartmentId = b.Id
WHERE 
	exists(
		select *
		from @Employee as c
		where c.DepartmentId = b.Id
		having MAX(c.Salary) = a.Salary
	)

用Postgresql 來玩玩

create table dept (
  id int not null primary key
, name text not null
);

create table emp (
  id int not null primary key
, name text not null
, salary int not null
, dept_id int not null references dept(id)
);

insert into dept values
(1, 'IT'), (2, 'Sales');

insert into emp values
(1, 'Joe', 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam', 60000, 2),
(4, 'Max', 90000, 1);

--
select dept_id
     , first(name order by salary desc)
     , first(salary order by salary desc)
  from emp
 group by dept_id; 
 
 dept_id | first | first 
---------+-------+-------
       1 | Max   | 90000
       2 | Henry | 80000
(2 筆資料列)

select d.name Department
     , a.employee
     , a.salary
  from (select dept_id
             , first(name order by salary desc) employee
             , first(salary order by salary desc) salary
          from emp
         group by dept_id) a
   join dept d
    on a.dept_id = d.id;       
    
 department | employee | salary 
------------+----------+--------
 IT         | Max      |  90000
 Sales      | Henry    |  80000
(2 筆資料列)

解法跟樓主差不多...... (我用MariaDB)

CREATE TABLE IF NOT EXISTS `Employee` (
	Id INT(8) NOT NULL AUTO_INCREMENT,
    Name VARCHAR(52) NOT NULL,
    Salary INT(8) NOT NULL,
    DepartmentId INT(8) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Department` (
	Id INT(8) NOT NULL AUTO_INCREMENT,
    Name VARCHAR(52) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Employee` 
(Name, Salary, DepartmentId) VALUES 
('Joe', 70000, 1),
('Henry', 80000, 2),
('Sam', 60000, 2),
('Max', 90000, 1);

INSERT INTO `Department` (Name) VALUES ('IT'), ('Sales');

SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary FROM `Employee` AS e
RIGHT JOIN (
	SELECT MAX(Salary) AS MaxSalary, DepartmentId AS DepId 
	FROM `Employee` 
	GROUP BY DepartmentId
) AS m ON e.Salary = m.MaxSalary AND e.DepartmentId = m.DepId
JOIN `Department` AS d ON e.DepartmentId = d.Id
ORDER BY Salary ASC;

我 ... ... 直接貼網站的解答公佈官方答案 ... ...

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

撿到現成的!

我是用MySQL 想問一下

select d.Name Department,e.Name Employee,MAX(e.Salary) Salary from employee e inner join department d on e.DepartmentId = d.Id GROUP BY e.DepartmentId

輸出的結果跟答案一樣為什麼還是錯誤的回答

我還是新手學生@@

痾 我找到錯誤了 輸出的人不同 不好意思QQ